package com.woniuxy.stage3demo.service.servicehpd.impl;

import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.woniuxy.stage3demo.bean.hpd.ChannelBean;
import com.woniuxy.stage3demo.mapper.mapperhpd.ChannelMapper;
import com.woniuxy.stage3demo.pageHelper.PageRequest;
import com.woniuxy.stage3demo.service.servicehpd.IChannelService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Transactional(readOnly = true)
@Service
public class ChannelServiceImpl implements IChannelService {
    @Resource
    private ChannelMapper channelMapper;


    @Transactional(isolation = Isolation.REPEATABLE_READ,rollbackFor = Exception.class)
    @Override
    public int addChannel(ChannelBean channelBean) {
        return channelMapper.addChannel(channelBean);
    }

    @Override
    public PageInfo<ChannelBean> findAllByParams(PageRequest request, ChannelBean channelBean) {
        return PageHelper.startPage(request.getPageNum(),request.getLimit()).
                doSelectPageInfo(()->channelMapper.findAllByParams(request,channelBean));
    }


    @Override
    public ChannelBean findById(Long id) {
        return channelMapper.findById(id);
    }


    @Transactional(isolation = Isolation.REPEATABLE_READ,rollbackFor = Exception.class)
    @Override
    public int updateChannelStatus(int opt,Long id) {
        return channelMapper.updateChannelStatus(opt,id);
    }

    @Transactional(isolation = Isolation.REPEATABLE_READ,rollbackFor = Exception.class)
    @Override
    public int updateChannel(ChannelBean channelBean) {
        return channelMapper.updateChannel(channelBean);
    }


    @Transactional(isolation = Isolation.REPEATABLE_READ,rollbackFor = Exception.class)
    @Override
    public int deleteChannel(Long id) {
        return channelMapper.deleteChannel(id);
    }

    @Transactional(isolation = Isolation.REPEATABLE_READ,rollbackFor = Exception.class)
    @Override
    public JSONObject batchImport(String fileName, MultipartFile file) throws Exception{
        JSONObject json_send = new JSONObject();
        boolean notNull = false;
        List<ChannelBean> channelList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            json_send.put("error", 0);
            return json_send;
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }

        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null) {
            notNull = true;
        }
        ChannelBean channelBean=new ChannelBean();
        for(int r = 2; r <= sheet.getLastRowNum(); r++){   //数据从第三行开始,从第三行开始循环
            Row row = sheet.getRow(r);
            if (row == null){   // getLastRowNum 可能不准确
                continue;
            }
            channelBean = new ChannelBean();

            if(row.getCell(0).getCellType() != 1){ //循环时，得到每一行的单元格进行判断
                row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
            }

            row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
            String id = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
            if(id == null || id.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "id为空");
                return json_send;
            }

            row.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
            String companyName = row.getCell(1).getStringCellValue();//得到每一行第二个单元格的值
            if(companyName == null || companyName.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "公司名称为空");
                return json_send;
            }

            row.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
            String companyArea = row.getCell(2).getStringCellValue();//得到每一行第三个单元格的值
            if(companyArea == null || companyArea.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "所在地区为空");
                return json_send;
            }

            row.getCell(3).setCellType(HSSFCell.CELL_TYPE_STRING);
            String channelLevel = row.getCell(3).getStringCellValue();//得到每一行第四个单元格的值
            if(channelLevel == null || channelLevel.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "级别为空");
                return json_send;
            }

            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_STRING);
            String contacts = row.getCell(4).getStringCellValue();//得到每一行第五个单元格的值
            if(contacts == null || contacts.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "联系人为空");
                return json_send;
            }

            row.getCell(5).setCellType(HSSFCell.CELL_TYPE_STRING);
            String contactsPhone = row.getCell(5).getStringCellValue();//得到每一行第六个单元格的值
            if(contactsPhone == null || contactsPhone.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "联系方式为空");
                return json_send;
            }

            row.getCell(6).setCellType(HSSFCell.CELL_TYPE_STRING);
            String source = row.getCell(6).getStringCellValue();//得到每一行第七个单元格的值
            if(source == null || source.isEmpty()){//判断是否为空
                json_send.put("error", 0);
                json_send.put("msg", "第" + r + "行,"+ "来源为空");
                return json_send;
            }


            channelBean.setId(Long.parseLong(id));
            channelBean.setCompanyName(companyName);    //组成对象
            channelBean.setCompanyArea(companyArea);
            channelBean.setChannelLevel(channelLevel);
            channelBean.setContacts(contacts);
            channelBean.setContactsPhone(contactsPhone);
            channelBean.setSource(source);
            channelList.add(channelBean);

        }
        for (ChannelBean channelResord : channelList) {
            long id = channelResord.getId();
            ChannelBean isChannel = channelMapper.findById(id);
            if (isChannel == null) {
                channelMapper.addChannel(channelResord);
                System.out.println(" 插入 "+channelResord);
            } else {
               channelMapper.updateChannel(channelResord);
                System.out.println(" 更新 "+channelResord);
            }
        }

        json_send.put("error", 1);
        json_send.put("msg", "成功");
        return json_send;
    }
}
